package org.bigjava.travel.provider;

import org.springframework.stereotype.Component;

/**
 * 攻略的SQL语句封装类
 */

@Component
public class StrategySqlProvider {

    //查询所有的攻略
    public String count(){ return "SELECT count(*) FROM t_strategy WHERE DELETE_STATUS=0"; }

    //通过ID来查询攻略
    public String findById() {
        return "SELECT * FROM t_strategy WHERE ID = #{id} AND DELETE_STATUS=0";
    }

    //查询所有的未删除的攻略并且通过ID来排序
    public String findList(){
        return "SELECT * FROM t_strategy WHERE DELETE_STATUS=0 ORDER BY ID ASC";
    }

    //查询所有的未删除并且已发布的攻略通过添加时间来排序
    public String indexList(){
        return "SELECT * FROM t_strategy WHERE DELETE_STATUS=0 AND STATE=1 ORDER BY ADD_TIME DESC";
    }

    //通过未删除攻略的标题来进行模糊查询
    public String findListByQuery(){
        return "SELECT * FROM t_strategy WHERE DELETE_STATUS=0 AND TITLE LIKE #{query,jdbcType=VARCHAR} ORDER BY ID ASC";
    }

    //修改攻略
    public String update(){
        return "UPDATE t_strategy SET TITLE=#{title},IMG_URL=#{imgUrl}," +
                "RATING=#{rating},SUMMARY=#{summary},INTRO_URL=#{introUrl},STATE=#{state}" +
                " WHERE ID=#{id}";
    }

    //官方发布攻略保存方法
    public String save(){
        return "INSERT INTO t_strategy(ADD_TIME,TITLE,IMG_URL,RATING,SUMMARY,INTRO_URL,STATE) " +
                "VALUES(NOW(),#{title},#{imgUrl},#{rating},#{summary},#{introUrl},#{state})";
    }

    //用户发布攻略的保存办法
    public String user_save(){
        return "INSERT INTO t_strategy(ADD_TIME,TITLE,IMG_URL,RATING,SUMMARY,INTRO_URL,STATE,ADD_USER) " +
                "VALUES(NOW(),#{title},#{imgUrl},#{rating},#{summary},#{introUrl},#{state},0)";
    }

    //通过ID删除攻略
    public String deleteById(){
        return "UPDATE t_strategy SET DELETE_STATUS=1 WHERE ID=#{id}";
    }


    public String state0count(){ return "SELECT count(*) FROM t_cms_strategy WHERE DELETE_STATUS=0 AND STATE=0"; }

    public String state1count(){ return "SELECT count(*) FROM t_cms_strategy WHERE DELETE_STATUS=0 AND STATE=1"; }

    public String state2count(){ return "SELECT count(*) FROM t_cms_strategy WHERE DELETE_STATUS=0 AND STATE=2"; }
}
